﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using System.Data;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.Reflection;
using System.ComponentModel;
using NPOI.XWPF.UserModel;

namespace CSharp.Infrastructure
{
    public class ExcelHelper : IDisposable
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public ExcelHelper()
        {
        }

        /// <summary>
        /// 导出到对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="file_path"></param>
        /// <param name="sheet_name"></param>
        /// <returns></returns>
        public List<T> ExcelToList<T>(string file_path, string sheet_name = "") where T : class, new()
        {
            IWorkbook workbook = null;
            List<T> result = new List<T>();
            try
            {
                using (var fs = new FileStream(file_path, FileMode.Open, FileAccess.Read))
                {
                    if (file_path.EndsWith(".xlsx")) // 2007版本
                        workbook = new XSSFWorkbook(fs);
                    else if (file_path.EndsWith(".xls")) // 2003版本
                        workbook = new HSSFWorkbook(fs);

                    ISheet sheet = null;
                    if (string.IsNullOrEmpty(sheet_name))
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    else
                    {
                        sheet = workbook.GetSheet(sheet_name);
                    }

                    if (sheet != null)
                    {
                        IRow firstRow = sheet.GetRow(0);
                        int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                        Dictionary<string, int> colIndexDic = new Dictionary<string, int>();
                        int startRow = sheet.FirstRowNum + 1;
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
                            colIndexDic.Add(column.ColumnName, i);
                        }

                        // 表列表,属性名称
                        Dictionary<string, PropertyInfo> proDic = new Dictionary<string, PropertyInfo>();
                        var ps = typeof(T).GetProperties();
                        foreach (var p in ps)
                        {
                            proDic.Add(p.Name, p);
                        }

                        //最后一列的标号
                        int rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            IRow row = sheet.GetRow(i);
                            if (row == null) continue; //没有数据的行默认是null　　　　　　　

                            T t = new T();
                            bool is_break = false;
                            foreach (var item in proDic)
                            {
                                if (row.GetCell(colIndexDic[item.Key]) != null)
                                {
                                    string text = row.GetCell(colIndexDic[item.Key]).ToString().Trim();
                                    item.Value.SetValue(t, text);
                                }
                            }

                            if (is_break)
                            {
                                break;
                            }

                            result.Add(t);
                        }
                    }

                    return result;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);

                return result;
            }
        }

        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="list">要导入的数据</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public byte[] DataTableToExcel<T>(List<T> list, string sheetName)
        {
            //创建SXSSFWorkbook对象
            XSSFWorkbook workbook = new XSSFWorkbook();

            DataTable data = ListToDataTable<T>(list);
            int i = 0;
            int j = 0;
            ISheet sheet = null;

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return new byte[0];
                }

                IRow row = sheet.CreateRow(0);
                for (j = 0; j < data.Columns.Count; ++j)
                {
                    row.CreateCell(j).SetCellValue(data.Columns[j].ToString());
                }

                for (i = 0; i < data.Rows.Count; ++i)
                {
                    row = sheet.CreateRow(i + 1);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        if (data.Rows[i][j] == null)
                        {
                            row.CreateCell(j).SetCellValue("");
                        }
                        else
                        {
                            row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                        }
                    }
                }

                using (var ms = new MemoryStream())
                {
                    workbook.Write(ms); //写入到excel

                    return ms.ToArray();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);

                return new byte[0];
            }
            finally
            {
                try
                {
                    workbook.Close();
                }
                catch { }
            }
        }

        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="list">要导入的数据</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public byte[] DataTableToExcel2<T>(List<T> list, string sheetName)
        {
            //创建SXSSFWorkbook对象
            XSSFWorkbook workbook = new XSSFWorkbook();

            DataTable data = ListToDataTable2(list);
            int i = 0;
            int j = 0;
            ISheet sheet = null;

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return new byte[0];
                }

                IRow row = sheet.CreateRow(0);
                for (j = 0; j < data.Columns.Count; ++j)
                {
                    row.CreateCell(j).SetCellValue(data.Columns[j].ToString());
                }

                for (i = 0; i < data.Rows.Count; ++i)
                {
                    row = sheet.CreateRow(i + 1);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        if (data.Rows[i][j] == null)
                        {
                            row.CreateCell(j).SetCellValue("");
                        }
                        else
                        {
                            row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                        }
                    }
                }

                using (var ms = new MemoryStream())
                {
                    workbook.Write(ms); //写入到excel

                    return ms.ToArray();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);

                return new byte[0];
            }
            finally
            {
                try
                {
                    workbook.Close();
                }
                catch { }
            }
        }

        public static string ReadExcel(string url_path, byte[] data, string sheet_name = null)
        {
            StringBuilder content = new StringBuilder();
            IWorkbook workbook = null;
            try
            {
                using (MemoryStream fs = new MemoryStream(data))
                {
                    if (url_path.EndsWith(".xlsx")) // 2007版本
                        workbook = new XSSFWorkbook(fs);
                    else if (url_path.EndsWith(".xls")) // 2003版本
                        workbook = new HSSFWorkbook(fs);

                    ISheet sheet = null;
                    if (string.IsNullOrEmpty(sheet_name))
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    else
                    {
                        sheet = workbook.GetSheet(sheet_name);
                    }

                    if (sheet != null)
                    {
                        for (int i = 0; i < sheet.LastRowNum; i++)
                        {
                            if (sheet.GetRow(i) != null)
                            {
                                for (int j = 0; j < sheet.GetRow(i).LastCellNum; j++)
                                {
                                    NPOI.SS.UserModel.ICell cell = sheet.GetRow(i).GetCell(j);
                                    if (cell != null)
                                    {
                                        cell.SetCellType(CellType.String);
                                        content.Append(cell.StringCellValue);
                                    }
                                }
                            }
                        }
                    }

                    return content.ToString();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);

                return content.ToString();
            }
        }

        /// <summary>
        /// List转换成DataTable
        /// </summary>
        /// <typeparam name="T">实体对象类型</typeparam>
        /// <param name="list">list集合</param>
        /// <returns>DataTable</returns>
        private static DataTable ListToDataTable<T>(List<T> list)
        {
            DataTable dt = new DataTable();
            foreach (T t in list)
            {
                DataRow dr = dt.NewRow();
                Type type = t.GetType();
                if (String.IsNullOrEmpty(dt.TableName))
                {
                    dt.TableName = type.Name;
                }

                foreach (var p in type.GetProperties())
                {
                    var attributes = (DescriptionAttribute[])p.GetCustomAttributes(typeof(DescriptionAttribute), false);
                    if (attributes.Length == 0)
                    {
                        continue;
                    }

                    string desc = attributes[0].Description;
                    if (!dt.Columns.Contains(desc))
                    {
                        dt.Columns.Add(desc);
                    }

                    dr[desc] = p.GetValue(t, null) == null ? "" : p.GetValue(t, null).ToString();
                }

                dt.Rows.Add(dr);
            }

            return dt;
        }

        /// <summary>
        /// List转换成DataTable
        /// </summary>
        /// <typeparam name="T">实体对象类型</typeparam>
        /// <param name="list">list集合</param>
        /// <returns>DataTable</returns>
        private static DataTable ListToDataTable2<T>(List<T> list)
        {
            DataTable dt = new DataTable();
            foreach (T t in list)
            {
                DataRow dr = dt.NewRow();
                Type type = t.GetType();
                if (String.IsNullOrEmpty(dt.TableName))
                {
                    dt.TableName = type.Name;
                }

                foreach (var p in type.GetProperties())
                {
                    string desc = p.Name;
                    if (!dt.Columns.Contains(desc))
                    {
                        dt.Columns.Add(desc);
                    }

                    dr[desc] = p.GetValue(t, null) == null ? "" : p.GetValue(t, null).ToString();
                }

                dt.Rows.Add(dr);
            }

            return dt;
        }

        /// <summary>
        /// 重写Dispose方法
        /// </summary>
        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }
    }
}
